I have a spreadsheet that uses a pivot table and refreshes on load and sets the date filter to yesterday.  I would like to have it set the date filter on Monday to Friday and Tuesday - Friday set to Yesterday.   I have created a cell formula that will give me the date I want to filter on.  =IF(WEEKDAY(TODAY())=2, TODAY()-3, TODAY()-1)  how do I get the pivot table to use the cell value as the date filter.  I would rather not use VBA but not apposed to it.  any help is greatly appreciated. 

Details: ActiveSheet.PivotTables("PivotTable3").PivotFields("Start Time")

Sheet / Cell: "=Summary!G1

Easy enough with regular PivotTable and VBA.
But PowerPivot and Power Query with "M" are more fun.
http://www.mediafire.com/view/adtuuxg35r77pzm/03_23_15.xlsx

PP and PQ are free Add-Ins for Excel 2010.
Compatible with Office 2013 Pro Plus.

How about creating an additional column ("Filter") in your source table for the Pivot that contains that logic - creating: yes/no as output?

Then a simple column or even report filter set to "yes" would do the job.

Power Pivot did not support text file with out .txt or .csv file extinction.  had not tried Power Query. 

can you provide example with just regular PivotTable. 

Here is what I have so far but it fails on the Field.CurrentPage = NewCat

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'This line stops the worksheet updating on every change, it only updates when cell
'G1 or G2 is touched
If Intersect(Target, Range("G1:G2")) Is Nothing Then Exit Sub
 
'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
 
'Here you amend to suit your data
Set pt = Worksheets("Summary").PivotTables("PivotTable3")
Set Field = pt.PivotFields("Start Time")
NewCat = Worksheets("Summary").Range("G1").Value
 
'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

Fixed:  Code to make it work. 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

''This line stops the worksheet updating on every change, it only updates when cell
''G2 or G3 is touched
'If Intersect(Target, Range("G2:G3")) Is Nothing Then Exit Sub
 

''Set the Variables to be used
Dim pt3 As PivotTable
Dim Field3 As PivotField
Dim NewDate3 As String

 
''Here you amend to suit your data
Set pt3 = Worksheets("Summary").PivotTables("PivotTable3")
Set Field3 = pt3.PivotFields("Start Time")
NewDate3 = Worksheets("Summary").Range("G1").Value


NewDate3 = Format(Date - 1, "dd-MMM")

If Weekday(Now()) = vbMonday Then

 NewDate3 = Format(Date - 3, "dd-MMM")
 
End If


''This updates and refreshes the PIVOT table
With pt3
Field3.ClearAllFilters
Field3.CurrentPage = NewDate3
pt3.RefreshTable
End With

End Sub